My job as a developer evangelist for Couchbase means than I work much less with SQL Server than I used to. However, it doesn't mean I don't keep up with it.

In fact, inspired by Couchbase, I decided to try out using SQL Server as a sort of document data store. SQL Server 2016 introduced some interesting new features that make this kinda possible: JSON_VALUE, JSON_QUERY, and JSON_MODIFY.

I set up a "document" table, which is two fields: a guid and an nvarchar(max). This is kinda like a Couchbase bucket: a key and a JSON document to go with it.

I put fairly complex hierarchical documents in these fields, something like:

I say "fairly complex", because representing this in a standard normalized fashion would require at least two tables, foreign keys, constraints, and then data migration and schema migration as the model evolves.

Now, suppose I want to execute a query and find all the document that are "Show=true". With Couchbase and N1QL, I would just use something like "SELECT * FROM `bucket` WHERE show = true".

With SQL Server, there's a little more work. "SELECT t.key, t.doc FROM [table] t WHERE JSON_VALUE(t.SpeakingInfo,'$.Show') = 'true'". Notice that JSON_VALUE is being applied to a text field, and a JSON path is used within JSON_VALUE to get a specific value from within that JSON object.

For this simple project I'm doing, that's all I need. No idea yet what kind of performance level I can expect from JSON_VALUE and more complex JSON paths.

But, this is definitely an example of the kinds of database convergence I've been telling people about. The separations between NoSQL and SQL are becoming less strict, at least in terms of data modeling and querying.

You need to be using SQL Server 2016 or SQL Server Azure to take advantage of the JSON_* functions.

I was recently on David Giard's show Technology and Friends! This is my second time on his show. This time I got to talk about NoSQL and Couchbase Server in particular. Check it out, and check out Couchbase Server.

Download Podcast 004 - Calvin Allen on Chocolatey NuGet

Subscribe:

• RSS
• iTunes
• Google Play
• Stitcher
• Spotify

Calvin Allen is a developer who is using Chocolatey NuGet to automate the setup of development environments and is also creating Chocolatey packages of his own.

Show notes:

• Chocolatey NuGet
• Chocolatey on GitHub
• Calvin mentioned these Chocolatey packages
  • IrfanView (IrfanView home page)
  • Couchbase Server (Couchbase home page)
• Calvin's profile on Chocolatey
• The Talking Devs podcast (Twitter)
• Code Bytes TV (YouTube)

Calvin Allen on Twitter

Want to be on the next episode? You can! All you need is the willingness to talk about something technical.

Theme music is "Crosscutting Concerns" by The Dirty Truckers, check out their music on Amazon or iTunes.